﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Model;
namespace DAL
{
    public class liberDAL
    {
        DBHelper db = new DBHelper();

        public DataTable GetDataByID(liber model)
        {
            string sql = string.Format("select * from [dbo].[liber] where liberID='{0}'", model.liberID);
            return db.GetData(sql).Tables[0];
        }
        public DataTable GetDataAll()
        {

            string sql = "select liberID, CuntID, liberName, liberMoney, liberConut, liberTitle, ManageID, liberCont,liberJianjie from [dbo].[liber]";
            return db.GetData(sql).Tables[0];
        }
        public List<liber> GetListALL() {
            try
            {
                string sql = $"select liberID,CuntID, liberName, liberMoney, liberConut, liberTitle,ManageID, liberCont from [dbo].[liber]";
                List<liber> list = null;
                DataTable dt = db.GetData(sql).Tables[0];
                if (dt.Rows.Count>0)
                {
                    list = new List<liber>();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        liber lbr = new liber();
                        lbr.liberID = Convert.ToInt32(dt.Rows[i]["liberID"]);
                        lbr.CuntID = Convert.ToInt32(dt.Rows[i]["CuntID"]);
                        lbr.liberName = dt.Rows[i]["liberName"].ToString();
                        lbr.liberMoney = dt.Rows[i]["liberMoney"].ToString();
                        lbr.liberConut = dt.Rows[i]["liberConut"].ToString();
                        lbr.liberTitle = dt.Rows[i]["liberTitle"].ToString();
                        lbr.ManageID = dt.Rows[i]["ManageID"].ToString();
                        lbr.liberCont = dt.Rows[i]["liberCont"].ToString();
                        list.Add(lbr);
                    }
                }
                return list;
            }
            catch (Exception)
            {

                return null;
            }
        }
        /// <summary>
        /// 显示
        /// </summary>
        /// <returns></returns>
        public List<liber> ShowNew()
        {
            try
            {
                string sql = $"select liberID,CuntID, liberName, liberMoney, liberConut, liberTitle,ManageID, liberCont from [dbo].[liber]";
                List<liber> list = null;
                DataTable tb = db.GetData(sql).Tables[0];
                if (tb.Rows.Count > 0)
                {
                    list = new List<liber>();
                    for (int i = 0; i < tb.Rows.Count; i++)
                    {
                        liber li = new liber();
                        li.liberID = Convert.ToInt32(tb.Rows[i]["liberID"]);
                        li.CuntID = Convert.ToInt32(tb.Rows[i]["CuntID"]);
                        li.liberName = tb.Rows[i]["liberName"].ToString();
                        li.liberMoney = tb.Rows[i]["liberMoney"].ToString();
                        li.liberConut = tb.Rows[i]["liberConut"].ToString();
                        li.liberTitle = tb.Rows[i]["liberTitle"].ToString();
                        li.ManageID = tb.Rows[i]["ManageID"].ToString();
                        li.liberCont = tb.Rows[i]["liberCont"].ToString();
                        list.Add(li);
                    }
                }
                return list;
            }
            catch (Exception)
            {

                return null;
            }
        }
        /// <summary>
        /// 添加书籍信息
        /// </summary>
        /// <param name="lb"></param>
        /// <returns></returns>
        public bool AddliberDAL(liber lb)
        {
            string sql = $"insert into [dbo].[liber](CuntID, liberName, liberMoney, liberConut, liberTitle,ManageID, liberCont)values('{lb.CuntID}','{lb.liberName}','{lb.liberMoney}','{lb.liberConut}','{lb.liberTitle}','{lb.ManageID}','{lb.liberCont}')";
            if (db.SetData(sql))
            {
                return true;
            }
            return false;
        }
        /// <summary>
        /// 书名查询
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public DataTable ShowSel(string name)
        {
            string sql = $"select liberID, CuntID, liberName, liberMoney, liberConut, liberTitle, ManageID, liberCont from [dbo].[liber] where liberName like'%{name}%'";
            return db.GetData(sql).Tables[0];
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool ShowUdd(liber lise)
        {
            string sql = $"update[dbo].[liber] set  CuntID='{lise.CuntID}', liberName='{lise.liberName}', liberMoney='{lise.liberMoney}', liberConut='{lise.liberConut}', liberTitle='{lise.liberTitle}', ManageID='{lise.ManageID}', liberCont='{lise.liberCont}' where [liberID]='{lise.liberID}' ";
            if (db.SetData(sql))
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        ///删除
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool Delete(liber lise)
        {
            string sql = $"delete  [dbo].[liber] where [liberID]='{lise.liberID}' ";
            if (db.SetData(sql))
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}
